package com.classes;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo {

	/**
	 * @param args
	 * @throws SQLException 
	 * @throws FileNotFoundException 
	 */
	public static void main(String[] args) throws SQLException, FileNotFoundException {
		// TODO Auto-generated method stub
		// Have to access tbl_classes
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");//Loading a class
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");//get the connection
			
			//Statement st = con.createStatement();//preparing statement object
			//ResultSet rs = st.executeQuery("select * from tbl_classes");//executing query
			
			CallableStatement cal = con.prepareCall("call insert_student(?, ?, ?)");
			cal.setInt(1, 601);
			cal.setString(2, "Rama");
			cal.setInt(3, 666);
			cal.execute();
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			con.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			con.rollback();
		} finally{//closing connections
			if(con != null){
				con.close();
			}		
		}
	}
	
	public void selectDemo(Connection con) throws SQLException{
		PreparedStatement ps = con.prepareStatement("select * from tbl_classes where class_id = ?");
		ps.setInt(1, 2);			
		ResultSet rs = ps.executeQuery();
		while(rs.next()){//iterate over result set
			System.out.println(rs.getString("class_id")+" : "+rs.getString("class_name"));
		}
		
		ps.setInt(1, 1);			
		rs = ps.executeQuery();
		while(rs.next()){//iterate over result set
			System.out.println(rs.getString("class_id")+" : "+rs.getString("class_name"));
		}
	}
	
	public void transactionDemo(Connection con) throws SQLException{
		con.setAutoCommit(false);
		PreparedStatement ps = con.prepareStatement("INSERT INTO tbl_credit(acc_no, balance) VALUES(?, ?);");
		ps.setInt(1, 103);
		ps.setInt(2, 5000);
		ps.executeUpdate();
		
		PreparedStatement ps2 = con.prepareStatement("INSERT INTO tbl_debit(acc_no, amount) VALUES(?, ?);");
		ps2.setInt(1, 102);
		ps2.setString(2, "abc");
		ps2.executeUpdate();
		
		con.commit();
	}
	
	public void demoBatchUpdate(Connection con) throws SQLException{
		PreparedStatement ps = con.prepareStatement("INSERT INTO tbl_student(number, NAME, marks) VALUES(?, ?, ? )");
		ps.setInt(1, 201);
		ps.setString(2, "xyz");
		ps.setInt(3, 600);
		ps.addBatch();
		
		ps.setInt(1, 202);
		ps.setString(2, "pqr");
		ps.setInt(3, 800);
		ps.addBatch();
		
		ps.executeBatch();	//execute inserts as one batch		
	}
	
	public void imageDemo(Connection con) throws SQLException, FileNotFoundException{

		PreparedStatement ps = con.prepareStatement("INSERT INTO tbl_student(number, NAME, marks, photo) VALUES(?, ?, ? , ?)");
		ps.setInt(1, 1001);
		ps.setString(2, "pqr");
		ps.setInt(3, 999);
		ps.setBinaryStream(4, new FileInputStream(new File("C:\\Users\\admin\\Pictures\\puppy.jpg")));
		ps.executeUpdate();
	}
}
